package test;

import org.junit.Test;

import java.sql.*;

/*
单元测试
 */
public class Jdbc01 {
    static final String Drivers = "com.mysql.cj.jdbc.Driver";
    static final String url = "jdbc:mysql://localhost:3378/mental_health";
    static final String user = "root";
    static final String password = "root";

    /**
     * 测试方法
     * JDBC（java数据库连接）
     * 1.加载驱动driver
     * 2.获取数据库连接connection
     * 3.创建sql语句对象（目的：执行sql语句）statement
     * 4.执行语句
     * 5.处理结果集
     * 6.关闭连接
     */
    @Test
    public void test1() throws ClassNotFoundException {

        try{
            //加载驱动driver
            Class.forName(Drivers);
            //获取数据库连接connection
            Connection conn= DriverManager.getConnection(url, user, password);
            //创建sql语句对象（目的：执行sql语句）statement
            Statement statement=conn.createStatement();
//            String sql="create table user(id int primary key auto_increment,name char(50) )";
//            boolean result=statement.execute(sql);

            statement.close();
            conn.close();
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }catch (Exception e){
            System.out.println(e.getMessage());
        }

        System.out.println("测试");
    }
    @Test
    public void InsertTest(){
        String sql="insert into user values (default,'Tom')";
        try{
            Class.forName(Drivers);
            Connection conn=DriverManager.getConnection(url, user, password);
            Statement statement=conn.createStatement();
            int result=statement.executeUpdate(sql);
            System.out.println(result);
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }
    @Test
    public void UpdateTest() throws ClassNotFoundException, SQLException {
        Class.forName(Drivers);
        Connection conn=DriverManager.getConnection(url, user, password);
        Statement statement=conn.createStatement();
        String sql="update user set name='Jerry' where id=1";
        int result=statement.executeUpdate(sql);
        System.out.println(result);
    }
    @Test
    public void DeleteTest() throws ClassNotFoundException, SQLException {
        Class.forName(Drivers);
        Connection conn=DriverManager.getConnection(url, user, password);
        Statement statement=conn.createStatement();
        String sql="delete from user where id=1";
        int result=statement.executeUpdate(sql);
        System.out.println(result);
    }
    @Test
    public void AddTest() throws ClassNotFoundException, SQLException {
        Class.forName(Drivers);
        Connection conn=DriverManager.getConnection(url, user, password);
        Statement statement=conn.createStatement();
       String sql="insert into user values ";
       int i=0;
       while(i<99){
            int a=(int)(Math.random()*99+1);
           sql+="(default,'Tom'),";
           i++;
       }
       sql+="(default,'Tom')";
       int n=statement.executeUpdate(sql);
        System.out.println( n);

    }
    @Test
    public void deletTest() throws ClassNotFoundException, SQLException {
        Class.forName(Drivers);
        Connection conn=DriverManager.getConnection(url, user, password);
        Statement statement=conn.createStatement();
        String sql = "DELETE FROM user WHERE id BETWEEN ? AND ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        // 4. 设置参数：删除 id 从 1 到 100 的用户
        pstmt.setInt(1, 1);
        pstmt.setInt(2, 100);

        // 5. 执行删除
        int rowsAffected = pstmt.executeUpdate();

        System.out.println("成功删除了 " + rowsAffected + " 条记录");

        // 6. 关闭资源（顺序重要）
        pstmt.close();
        conn.close();
    }
    @Test
    public void selectAll() throws ClassNotFoundException, SQLException {
        Class.forName(Drivers);
        Connection conn=DriverManager.getConnection(url, user, password);
        Statement statement=conn.createStatement();
        String sql="select id,name from user";
        ResultSet resultSet=statement.executeQuery(sql);
        while(resultSet.next()){
            int id=resultSet.getInt("id");
            String name=resultSet.getString("name");
            System.out.println(id+" "+name);
        }
        resultSet.close();
        statement.close();
        conn.close();

    }
}

